package com.relife.core.utils;

import java.text.MessageFormat;

import com.relife.common.model.Page;
import com.relife.core.spring.SpringContextUtil;
import org.apache.log4j.Logger;
import org.springframework.orm.hibernate4.LocalSessionFactoryBean;



public class DBUtil {
	public static Logger log = Logger.getLogger(DBUtil.class);
	/**
	 * 分页SQL
	 */
	public static String MYSQL_SQL = "select * from ({0}) sel_tab00 limit {1},{2}"; // mysql
	public static String SQLSERVER_SQL = "select * from (select row_number() over({3}) as LINENUM,* from ({0}) t ) d where d.LineNum between {1} and {2}";

	public static String getDBType() {
		LocalSessionFactoryBean sf = (LocalSessionFactoryBean) SpringContextUtil.getService("&sessionFactory");
		String dbdialect = sf.getHibernateProperties().getProperty("hibernate.dialect");
		log.debug(dbdialect);
		String retStr = null;
		if (dbdialect.equals("org.hibernate.dialect.MySQLDialect")) {
			retStr = "mysql";
		} else if (dbdialect.contains("Oracle")) {// oracle有多个版本的方言
			retStr = "oracle";
		} else if (dbdialect.contains("SQLServer")) {
			retStr = "sqlserver";
		} else if (dbdialect.equals("org.hibernate.dialect.PostgreSQLDialect")) {
			retStr = "postgres";
		}
		return retStr;
	}

	/**
	 * 
	 * @author WU
	 * @date 2016年1月14日 上午10:47:07
	 * @param sql
	 */
	public static String formatPageSql(String sql, Page p) {
		int beginIndex = (p.getPage() - 1) * p.getRows();
		int endIndex = beginIndex + p.getRows();
		if ("mysql".equals(getDBType())) {
			sql = MessageFormat.format(MYSQL_SQL, sql, beginIndex, p.getRows());

		} else if ("oracle".equals(getDBType())) {
			if (p.getPage() == 1) {
				sql = "select * from (" + sql + ") where rownum <=" + p.getRows();
			} else {
				sql = "select * from (select row_.*, rownum rownum_ from (" + sql + ") row_) where rownum_ <=" + endIndex + " and rownum_ > " + beginIndex;
			}

		} else if ("sqlserver".equals(getDBType())) {
			String order = " order by " + p.getSort() + " " + p.getOrder();
			sql = MessageFormat.format(SQLSERVER_SQL, sql, beginIndex + 1, endIndex, order);

		}
		return sql;
	}
}
